#!/use/bin/python
# coding=utf-8
# 主动服务 覆盖设备量
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num

def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate


# 记录 主动服务-各服务可见用户数 analysis_feed_number
#date 当天 beforeDate 昨天 serId 服务
def insertFeedNumber(date,beforeDate,serId):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_feed_number 表是否已经存在当天 早间开机的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_feed_number t where 1=1 and t.service_key ='{serId}' and t.ref_date =  '{date}'")
        index = serIdToStateIndex(serId)
        # 获取当日早间服务可见数量
        cumulateNum = selectNumBySql(
            f"select count(1) subNum from life_assistant_devices t where (switches_state1 & (1 << ({index} - 1)))<>0 ",1)
        #计算与昨天的可见量差值 作为新增的数量
        resultBeforeNum = selectNumBySql(
            f"select cumulate_num from analysis_feed_number t where t.service_key ='{serId}' and t.ref_date =  '{beforeDate}'")
        newNum = cumulateNum - resultBeforeNum
        # 获取当天订阅量
        subscribeNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='{serId}' and t.ref_date =  '{date}' and t.execute_flag = 0")
        #开启率
        openRate = getRateByXY(subscribeNum,cumulateNum)
        serName = serIdToName(serId)

        if countNum == 0:
            logDetail = f"{date}日新增-{serName}覆盖设备累计量为{cumulateNum},新增设备量为{newNum},订阅量{subscribeNum},开启率{openRate}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key,open_rate) 
                value({newNum},{cumulateNum},'{date}','{serId}',{openRate})"""
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-{serName}覆盖设备累计量为{cumulateNum},新增设备量为{newNum},订阅量{subscribeNum},开启率{openRate}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_feed_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now(), open_rate = '{openRate}'  where
                    ref_date = '{date}' and service_key ='{serId}' 
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 主动服务-3个服务任意服务(总体)可见用户数 analysis_feed_number
def insertAllFeedNumber(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        # 判断 analysis_feed_number 表是否已经存在当天 早间开机的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_feed_number t where 1=1 and t.service_key ='all' and t.ref_date =  '{date}'")
        # 获取当日早间服务可见数量
        cumulateNum = selectNumBySql(
            f"""select count(1) subNum from life_assistant_devices t where (switches_state1 & (1 << (1 - 1)))<>0 or 
                   (switches_state1 & (1 << (3 - 1)))<>0 or (switches_state1 & (1 << (5 - 1)))<>0 or (switches_state1 & (1 << (7 - 1)))<>0
                    or (switches_state1 & (1 << (9 - 1)))<>0 or (switches_state1 & (1 << (19 - 1)))<>0 """, 1)
        # 计算与昨天的可见量差值 作为新增的数量
        resultBeforeNum = selectNumBySql(
            f"select cumulate_num from analysis_feed_number t where t.service_key ='all' and t.ref_date =  '{beforeDate}'")
        newNum = cumulateNum - resultBeforeNum
        # 获取当天订阅量
        subscribeNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='all' and t.ref_date =  '{date}' and t.execute_flag = 0")
        #开启率
        openRate = getRateByXY(subscribeNum,cumulateNum)

        if countNum == 0:
            logDetail = f"{date}日新增-生活助手总体覆盖设备累计量为{cumulateNum},新增设备量为{newNum},订阅量{subscribeNum},开启率{openRate}"
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = f"""insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key,open_rate) 
                value({newNum},{cumulateNum},'{date}','all',{openRate})"""
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-生活助手总体覆盖设备累计量为{cumulateNum},新增设备量为{newNum},订阅量{subscribeNum},开启率{openRate}"
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = f"""
                update analysis_feed_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now() , open_rate = '{openRate}'  where
                    ref_date = '{date}' and service_key ='all'
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 当月 主动服务-各服务可见用户数 analysis_feed_number
def insertFeedNumberMonth(toMonthDay,serId):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_feed_number 表是否已经存在当月 早间开机的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_feed_number t where 1=1 and t.service_key ='{serId}' and t.ref_date =  '{toMonthDay}'")
        index = serIdToStateIndex(serId)
        # 获取当日早间服务可见数量,作为本月的可见数量
        cumulateNum = selectNumBySql(
            f"select count(1) subNum from life_assistant_devices t where (switches_state1 & (1 << ({index} - 1)))<>0 ", 1)
        #计算当月累计新增的数量
        newNum = selectNumBySql(
            f"select sum(new_num) from analysis_feed_number t  where SUBSTR( t.ref_date, 1,7) = '{toMonthDay}' and LENGTH(t.ref_date) = 10   and service_key ='{serId}'")
        serName = serIdToName(serId)

        if countNum == 0:
            logDetail = f"{toMonthDay}月新增-{serName}覆盖设备累计量为{cumulateNum},新增设备量为{newNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value({newNum},{cumulateNum},'{toMonthDay}','{serId}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{toMonthDay}月更新-{serName}覆盖设备累计量为{cumulateNum},新增设备量为{newNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_feed_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now()  where
                    ref_date = '{toMonthDay}' and service_key ='{serId}'
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 当月 主动服务--3个服务任意服务(总体)可见用户数 analysis_feed_number
def insertAllSubscribeNumberMonth(toMonthDay):
    try:
        conn, cur = getDataConnection()
        # 判断 analysis_feed_number 表是否已经存在当月 早间开机的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_feed_number t where 1=1 and t.service_key ='all' and t.ref_date =  '{toMonthDay}'")
        # 获取当日早间服务可见数量,作为本月的可见数量
        cumulateNum = selectNumBySql(
            f"""select count(1) subNum from life_assistant_devices t where (switches_state1 & (1 << (1 - 1)))<>0 or 
                   (switches_state1 & (1 << (3 - 1)))<>0 or (switches_state1 & (1 << (5 - 1)))<>0 or (switches_state1 & (1 << (7 - 1)))<>0
                    or (switches_state1 & (1 << (9 - 1)))<>0 """, 1)
        # 计算当月累计新增的数量
        newNum = selectNumBySql(
            f"select sum(new_num) from analysis_feed_number t  where SUBSTR( t.ref_date, 1,7) = '{toMonthDay}' and LENGTH(t.ref_date) = 10   and service_key ='all' ")

        if countNum == 0:
            logDetail = f"{toMonthDay}月新增-生活助手总体覆盖设备累计量为{cumulateNum},新增设备量为{newNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_feed_number(new_num,cumulate_num,ref_date,service_key) 
                value({newNum},{cumulateNum},'{toMonthDay}','all')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{toMonthDay}月更新-生活助手总体覆盖设备累计量为{cumulateNum},新增设备量为{newNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_feed_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now()  where
                    ref_date = '{toMonthDay}' and service_key ='all'
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#根据serId 获取服务中文名
def serIdToName(serId):
    if serId =='zjkj':
        serName = "早间开机"
    elif serId =='jkzx':
        serName = "健康作息"
    elif serId =='yjyl':
        serName = "夜间音量"
    elif serId =='zjtx':
        serName = "追剧提醒"
    elif serId =='sjtx':
        serName = "睡觉提醒"
    elif serId =='jykj':
        serName = "静音开机"
    else:
        serName = "其他"
    return serName

#根据serId 获取开关位置
def serIdToStateIndex(serId):
    if serId =='zjkj':
        index = 1
    elif serId =='yjyl':
        index = 3
    elif serId =='jkzx':
        index = 5
    elif serId =='zjtx':
        index = 7
    elif serId =='sjtx':
        index = 9
    elif serId =='jykj':
        index = 19
    return index

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 27
        jobName = '核心指标-生活助手-覆盖用户数'
        tableName = 'analysis_feed_number'
        # 插入日志表
        saveLogSql = f"""insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                        value({jobId},'{jobName}','{detail}','{tableName}','{ref_date}')"""
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    date = getdate(1)
    beforeDate = getdate(2)
    monthdate = getMonthdate(1)
    print (f"{date} 日期,更新<生活助手 覆盖设备量>报表")
    #insertFeedNumber(date,beforeDate,'zjkj')
    #insertFeedNumber(date,beforeDate,'jkzx')
    #insertFeedNumber(date,beforeDate,'yjyl')
    #insertFeedNumber(date,beforeDate,'zjtx')
    #insertFeedNumber(date,beforeDate, 'sjtx')
    insertFeedNumber(date, beforeDate,'jykj')
    insertAllFeedNumber(date,beforeDate)
    #insertFeedNumberMonth(monthdate,'zjkj')
    #insertFeedNumberMonth(monthdate,'jkzx')
    #insertFeedNumberMonth(monthdate,'yjyl')
    #insertFeedNumberMonth(monthdate,'zjtx')
    #insertFeedNumberMonth(monthdate,'sjtx')
    #insertAllSubscribeNumberMonth(monthdate)
